
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, 2012 and Azure
-- --------------------------------------------------
-- Date Created: 11/30/2014 09:59:40
-- Generated from EDMX file: \\vmware-host\Shared Folders\Documents\Visual Studio 2012\Projects\Check Printing Solution\check printing solution\EntityModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [ChequePrintingDB];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_BankCustomer]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Customers] DROP CONSTRAINT [FK_BankCustomer];
GO
IF OBJECT_ID(N'[dbo].[FK_ChequeTypeLayout]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TemplateLayouts] DROP CONSTRAINT [FK_ChequeTypeLayout];
GO
IF OBJECT_ID(N'[dbo].[FK_RoleApplicationUser]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ApplicationUsers] DROP CONSTRAINT [FK_RoleApplicationUser];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Banks]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Banks];
GO
IF OBJECT_ID(N'[dbo].[Customers]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Customers];
GO
IF OBJECT_ID(N'[dbo].[ChequeTypes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ChequeTypes];
GO
IF OBJECT_ID(N'[dbo].[TemplateLayouts]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TemplateLayouts];
GO
IF OBJECT_ID(N'[dbo].[ApplicationUsers]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ApplicationUsers];
GO
IF OBJECT_ID(N'[dbo].[ApplicationLogs]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ApplicationLogs];
GO
IF OBJECT_ID(N'[dbo].[Roles]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Roles];
GO
IF OBJECT_ID(N'[dbo].[JobLogs]', 'U') IS NOT NULL
    DROP TABLE [dbo].[JobLogs];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Banks'
CREATE TABLE [dbo].[Banks] (
    [Id] nvarchar(50)  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [BranchPrefix] nvarchar(max)  NOT NULL,
    [BranchCode] nvarchar(max)  NOT NULL,
    [BranchSequence] nvarchar(max)  NOT NULL,
    [ChequeType] nvarchar(max)  NOT NULL,
    [BankAddress] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Customers'
CREATE TABLE [dbo].[Customers] (
    [Id] nvarchar(50)  NOT NULL,
    [FirstName] nvarchar(max)  NOT NULL,
    [LastName] nvarchar(max)  NOT NULL,
    [Line2] nvarchar(max)  NOT NULL,
    [Line3] nvarchar(max)  NOT NULL,
    [ChequeNumber] nvarchar(max)  NOT NULL,
    [BankId] nvarchar(50)  NOT NULL,
    [Line1] nvarchar(max)  NULL,
    [Ext] nvarchar(max)  NULL,
    [Enabled] bit  NOT NULL,
    [Line4] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'ChequeTypes'
CREATE TABLE [dbo].[ChequeTypes] (
    [Id] nvarchar(50)  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [UseForAll] bit  NOT NULL
);
GO

-- Creating table 'TemplateLayouts'
CREATE TABLE [dbo].[TemplateLayouts] (
    [Id] nvarchar(50)  NOT NULL,
    [ChequeTypeId] nvarchar(50)  NOT NULL,
    [NameX] nvarchar(max)  NOT NULL,
    [NameY] nvarchar(max)  NOT NULL,
    [NameFont] nvarchar(max)  NOT NULL,
    [NameFontSize] nvarchar(max)  NOT NULL,
    [Address1X] nvarchar(max)  NOT NULL,
    [Address1Y] nvarchar(max)  NOT NULL,
    [Address1Font] nvarchar(max)  NOT NULL,
    [Address1FontSize] nvarchar(max)  NOT NULL,
    [Address2X] nvarchar(max)  NOT NULL,
    [Address2Y] nvarchar(max)  NOT NULL,
    [Address2Font] nvarchar(max)  NOT NULL,
    [Address2FontSize] nvarchar(max)  NOT NULL,
    [ChequeNumberX] nvarchar(max)  NOT NULL,
    [ChequeNumberY] nvarchar(max)  NOT NULL,
    [ChequeNumberFont] nvarchar(max)  NOT NULL,
    [ChequeNumberFontSize] nvarchar(max)  NOT NULL,
    [BankX] nvarchar(max)  NOT NULL,
    [BankY] nvarchar(max)  NOT NULL,
    [BankFont] nvarchar(max)  NOT NULL,
    [BankFontSize] nvarchar(max)  NOT NULL,
    [ChquesPerPage] nvarchar(max)  NOT NULL,
    [DepositSlips] nvarchar(max)  NOT NULL,
    [UseCover] bit  NOT NULL,
    [PrintChequeNos] bit  NOT NULL,
    [PrintStubNos] bit  NOT NULL,
    [PrintBranch] bit  NOT NULL,
    [PrintNames] bit  NOT NULL,
    [DistanceBetweenCheques] nvarchar(max)  NOT NULL,
    [PrintBranchAddress] bit  NOT NULL,
    [PrintNameAddress] bit  NOT NULL,
    [PrintCovers] bit  NOT NULL,
    [CoverNameX] nvarchar(max)  NOT NULL,
    [CoverNameY] nvarchar(max)  NOT NULL,
    [CoverNameFont] nvarchar(max)  NOT NULL,
    [CoverNameSize] nvarchar(max)  NOT NULL,
    [CoverAddress1X] nvarchar(max)  NOT NULL,
    [CoverAddress1Y] nvarchar(max)  NOT NULL,
    [CoverAddress1Font] nvarchar(max)  NOT NULL,
    [CoverAddress1Size] nvarchar(max)  NOT NULL,
    [CoverAddress2X] nvarchar(max)  NOT NULL,
    [CoverAddress2Y] nvarchar(max)  NOT NULL,
    [CoverAddress2Font] nvarchar(max)  NOT NULL,
    [CoverAddress2Size] nvarchar(max)  NOT NULL,
    [CoverTelephoneX] nvarchar(max)  NOT NULL,
    [CoverTelephoneY] nvarchar(max)  NOT NULL,
    [CoverTelephoneFont] nvarchar(max)  NOT NULL,
    [CoverTelephoneSize] nvarchar(max)  NOT NULL,
    [Landscape] bit  NOT NULL,
    [ChequeCountX] nvarchar(max)  NOT NULL,
    [ChequeCountY] nvarchar(max)  NOT NULL,
    [ChequeCountFont] nvarchar(max)  NOT NULL,
    [ChequeCountFontSize] nvarchar(max)  NOT NULL,
    [CoverCheckNoX] nvarchar(max)  NOT NULL,
    [CoverCheckNoY] nvarchar(max)  NOT NULL,
    [CoverCheckNoFont] nvarchar(max)  NOT NULL,
    [CoverCheckNoSize] nvarchar(max)  NOT NULL,
    [CoverCheckCountX] nvarchar(max)  NOT NULL,
    [CoverCheckCountY] nvarchar(max)  NOT NULL,
    [CoverCheckCountFont] nvarchar(max)  NOT NULL,
    [CoverCheckCountSize] nvarchar(max)  NOT NULL,
    [Address3X] nvarchar(max)  NOT NULL,
    [Address3Y] nvarchar(max)  NOT NULL,
    [Address3Font] nvarchar(max)  NOT NULL,
    [Address3Size] nvarchar(max)  NOT NULL,
    [Address4X] nvarchar(max)  NOT NULL,
    [Address4Y] nvarchar(max)  NOT NULL,
    [Address4Font] nvarchar(max)  NOT NULL,
    [Address4Size] nvarchar(max)  NOT NULL,
    [CoverAddress3X] nvarchar(max)  NOT NULL,
    [CoverAddress3Y] nvarchar(max)  NOT NULL,
    [CoverAddress3Font] nvarchar(max)  NOT NULL,
    [CoverAddress3Size] nvarchar(max)  NOT NULL,
    [CoverAddress4X] nvarchar(max)  NOT NULL,
    [CoverAddress4Y] nvarchar(max)  NOT NULL,
    [CoverAddress4Font] nvarchar(max)  NOT NULL,
    [CoverAddress4Size] nvarchar(max)  NOT NULL,
    [NoChequesPerBook] nvarchar(max)  NOT NULL,
    [SeqNumberSize] nvarchar(max)  NOT NULL,
    [BoldNameCheque] bit  NOT NULL,
    [BoldNameCover] bit  NOT NULL,
    [StubX] nvarchar(max)  NOT NULL,
    [StubY] nvarchar(max)  NOT NULL,
    [StubFont] nvarchar(max)  NOT NULL,
    [StubSize] nvarchar(max)  NOT NULL,
    [PrintStubNos2] bit  NOT NULL,
    [Skip] bit  NOT NULL,
    [CheckNoCharX] nvarchar(max)  NULL,
    [CheckNoCharY] nvarchar(max)  NULL,
    [CheckNoCharFont] nvarchar(max)  NULL,
    [CheckNoCharSize] nvarchar(max)  NULL,
    [CheckNo2X] nvarchar(max)  NULL,
    [CheckNo2Y] nvarchar(max)  NULL,
    [CheckNo2Font] nvarchar(max)  NULL,
    [CheckNo2Size] nvarchar(max)  NULL,
    [NoCopies] int  NOT NULL,
    [CoverCount] int  NOT NULL,
    [BoldLine2Cheque] bit  NOT NULL,
    [BoldLine3Cheque] bit  NOT NULL,
    [BoldLine4Cheque] bit  NOT NULL,
    [ItalicLine1Cheque] bit  NOT NULL,
    [ItalicLine2Cheque] bit  NOT NULL,
    [ItalicLine3Cheque] bit  NOT NULL,
    [ItalicLine4Cheque] bit  NOT NULL,
    [ItalicLine1Cover] bit  NOT NULL,
    [ItalicLine2Cover] bit  NOT NULL,
    [ItalicLine3Cover] bit  NOT NULL,
    [ItalicLine4Cover] bit  NOT NULL,
    [PaperWidth] int  NOT NULL,
    [PaperHeight] int  NOT NULL,
    [Tray] nvarchar(max)  NOT NULL,
    [PrintDearCustomer] bit  NOT NULL
);
GO

-- Creating table 'ApplicationUsers'
CREATE TABLE [dbo].[ApplicationUsers] (
    [Id] uniqueidentifier  NOT NULL,
    [UserName] nvarchar(max)  NOT NULL,
    [Password] nvarchar(max)  NOT NULL,
    [RoleId] int  NOT NULL
);
GO

-- Creating table 'ApplicationLogs'
CREATE TABLE [dbo].[ApplicationLogs] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Event] nvarchar(max)  NOT NULL,
    [EventUser] nvarchar(max)  NOT NULL,
    [EventTime] datetime  NOT NULL
);
GO

-- Creating table 'Roles'
CREATE TABLE [dbo].[Roles] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [RoleName] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NULL
);
GO

-- Creating table 'JobLogs'
CREATE TABLE [dbo].[JobLogs] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Comment] nvarchar(max)  NOT NULL,
    [DatePrinted] datetime  NOT NULL,
    [LastQuantity] nvarchar(max)  NOT NULL,
    [LastChequeNumber] nvarchar(max)  NOT NULL,
    [CustomerId] nvarchar(50)  NOT NULL,
    [ChequeTypeId] nvarchar(50)  NOT NULL,
    [Bank] nvarchar(max)  NULL,
    [Branch] nvarchar(max)  NULL,
    [NumberBooks] nvarchar(max)  NULL,
    [Filename] nvarchar(max)  NULL,
    [CustomerName] nvarchar(max)  NULL,
    [PrintUser] nvarchar(max)  NULL,
    [NumberCheques] nvarchar(max)  NULL,
    [IsReprint] bit  NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Banks'
ALTER TABLE [dbo].[Banks]
ADD CONSTRAINT [PK_Banks]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Customers'
ALTER TABLE [dbo].[Customers]
ADD CONSTRAINT [PK_Customers]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ChequeTypes'
ALTER TABLE [dbo].[ChequeTypes]
ADD CONSTRAINT [PK_ChequeTypes]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'TemplateLayouts'
ALTER TABLE [dbo].[TemplateLayouts]
ADD CONSTRAINT [PK_TemplateLayouts]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ApplicationUsers'
ALTER TABLE [dbo].[ApplicationUsers]
ADD CONSTRAINT [PK_ApplicationUsers]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ApplicationLogs'
ALTER TABLE [dbo].[ApplicationLogs]
ADD CONSTRAINT [PK_ApplicationLogs]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Roles'
ALTER TABLE [dbo].[Roles]
ADD CONSTRAINT [PK_Roles]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'JobLogs'
ALTER TABLE [dbo].[JobLogs]
ADD CONSTRAINT [PK_JobLogs]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [BankId] in table 'Customers'
ALTER TABLE [dbo].[Customers]
ADD CONSTRAINT [FK_BankCustomer]
    FOREIGN KEY ([BankId])
    REFERENCES [dbo].[Banks]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_BankCustomer'
CREATE INDEX [IX_FK_BankCustomer]
ON [dbo].[Customers]
    ([BankId]);
GO

-- Creating foreign key on [ChequeTypeId] in table 'TemplateLayouts'
ALTER TABLE [dbo].[TemplateLayouts]
ADD CONSTRAINT [FK_ChequeTypeLayout]
    FOREIGN KEY ([ChequeTypeId])
    REFERENCES [dbo].[ChequeTypes]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_ChequeTypeLayout'
CREATE INDEX [IX_FK_ChequeTypeLayout]
ON [dbo].[TemplateLayouts]
    ([ChequeTypeId]);
GO

-- Creating foreign key on [RoleId] in table 'ApplicationUsers'
ALTER TABLE [dbo].[ApplicationUsers]
ADD CONSTRAINT [FK_RoleApplicationUser]
    FOREIGN KEY ([RoleId])
    REFERENCES [dbo].[Roles]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_RoleApplicationUser'
CREATE INDEX [IX_FK_RoleApplicationUser]
ON [dbo].[ApplicationUsers]
    ([RoleId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------